using System;
using System.IO;
using System.Web;
using System.Data;
using System.Collections.Specialized;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;

namespace LdExpress
{
    public class ImportDB
    {
        public static string strTableName = "";
        public static string strServerPath = "";
        public static string strCsvDirOnServer = "";
        public static string strExportCSV = "";
        public static string strExportTable = "";
        public static string strExportCSVDir = "";
        public static string strExportAsCsvOrText = "";
        private FileInfo fil;

        public string TableName { get { return strTableName; } set { strTableName = value; } }
        public string CsvDirOnServer { get { return strCsvDirOnServer; } set { strCsvDirOnServer = value; } }
        public FileInfo FileInformation { get { return fil; } set { fil = value; } }
        
        public ImportDB()
        {
        }
        

        public static void SaveFileToServer(string fileName, HttpPostedFile inpFile)
        {

            string FileDirPhysicalPath = System.Web.HttpContext.Current.Server.MapPath("/") + "\\Import\\";
            string RelativePath = @"Import\";
            string FileFullPath = "";
            string ShortFileName = "";
            string strSQL = "";

            Document.CheckDirectory(FileDirPhysicalPath + fileName);
            RelativePath += fileName + @"\";

            if (inpFile != null)
            {
                if (System.IO.File.Exists(FileFullPath)) System.IO.File.Delete(FileFullPath);
                if (inpFile.FileName.Length > 0)
                {
                    ShortFileName = Document.GetShortFileName(inpFile.FileName);
                    FileFullPath = FileDirPhysicalPath + "\\" + ShortFileName;
                    RelativePath += ShortFileName;
                }
            }
            else
            {
                FileFullPath = FileDirPhysicalPath + "\\" + ShortFileName;
                RelativePath += ShortFileName;
            }
            try
            {
                if (inpFile != null)
                {
                    inpFile.SaveAs(FileFullPath);
                }
            }
            catch { }
        }
        public static DataSet ConnectFile(FileInfo filetable)
        {
            
            DataSet ds = new DataSet();
            string cnstr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';Data Source=";
            cnstr += System.Web.HttpContext.Current.Server.MapPath("/") + "\\Import\\" + @"\" + filetable.Name.ToString();

            OleDbConnection newcn = new OleDbConnection(cnstr);
            newcn.Open();
            
            string sqlSelect;
            string str;

            try
            {
                sqlSelect = "select * from [Sheet1$]";
                OleDbCommand cmd = new OleDbCommand(sqlSelect, newcn);
                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                
                adapter.Fill(ds);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                newcn.Close();
            }
            return ds;

        }
    }
}